sql基础提高(一)

create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);


insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;


insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;


insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;


insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;
 
1.查询c001课程比c002课程成绩高的学生的学号
以下为两种查询方法
select a.sno from
(select * from sc where cno='c001') a,
(select * from sc where cno='c002') b
where a.sno=b.sno and a.score>b.score;
#####################################
select a.sno from sc a where a.cno='c001' and exists (select * from sc b where b.cno='c002' and a.sno=b.sno and a.score>b.score);
2.查看平均成绩大于60的学生的学号和平均成绩
 


备注:分组语句，使用组函数avg(); 所有未包换在组函数的列都应该包含在group by 子句中，而包换在group by 子句的中列可以不包含在select列表中
3.查询所有同学的学号，姓名，选课数，总成绩
以下为两种查询方法
 select a.sno,a.sname,count(b.cno),sum(b.score) from student a,sc b where a.sno=b.sno group by a.sno,a.sname order by sum(b.score) desc;
#############################
select b.*,a.sname from student a,(select sno,count(cno),sum(score) from sc group by sno) b where a.sno=b.sno;
4.查询姓刘的老师的个数
select count(*) from teacher where tname like '刘%';
5.查询没有学过谌燕老师课的所有学生的学号，姓名
 
6.查询学过'c001'并且也学过'c002'课程的学生的学号，姓名
select c.sno,c.sname from
sc a join sc b on a.sno=b.sno join student c on a.sno=c.sno and a.cno='c001' and b.cno='c002';
备注：分别用到自连接和内连接
7.查询学过谌燕老师所有课程的学生的学号和姓名
以下为三种查询方法
select st.sno,st.sname from student st where
st.sno in
(select sno from sc a join course b on a.cno=b.cno join teacher c on b.tno=c.tno and c.tname='谌燕');
 
##############################
select distinct st.sno,st.sname from student st
join sc a on st.sno=a.sno join course b on a.cno=b.cno join teacher c on b.tno=c.tno and c.tname='谌燕' order by st.sno;
##############################
select distinct st.sno,st.sname from student st,sc a,course b,teacher c
where st.sno=a.sno and a.cno=b.cno and b.tno=c.tno and c.tname='谌燕' order by st.sno;
8.查看课程号为'c002'的成绩课程号为'c001'的成绩低的所有学生的学号和姓名
以下为三种查询方法
select st.sno,st.sname from student st where
st.sno in 
(select a.sno from sc a join sc b on a.sno=b.sno and a.cno='c001' and b.cno='c002' and a.score>b.score);
##############################
select st.sno,st.sname from student st,sc a,sc b where st.sno=a.sno and a.sno=b.sno and a.cno='c001' and b.cno='c002' and a.score>b.score;
##############################
select st.sno,st.sname from student st join sc a on st.sno=a.sno join sc b on a.sno=b.sno and a.cno='c001' and b.cno='c002' and a.score>b.score;
9.查询课程成绩小于60分的所有同学的学号和姓名
以下为两种查询方法
select a.sno,a.sname,b.score from student a,sc b where a.sno=b.sno and b.score<60;
##############################
select st.sno,st.sname from student st where
st.sno in (select a.sno from sc a where  a.score<60 and a.sno=st.sno);
10.查询没有学全所有课的同学的学号和姓名
以下为三种查询方法
select st.sno,st.sname,count(a.cno) from student st,sc a where st.sno=a.sno(+) group by st.sno,st.sname having count(a.cno)<=
(select count(cno) from course) order by count(a.cno) desc;
##############################
select st.sno,st.sname,count(a.cno) from student st  left join sc a on st.sno=a.sno group by st.sno,st.sname having count(a.cno) <=
(select count(cno) from course) order by count(a.cno) desc;
备注：左外连接、having：分组过滤条件，满足having条件的分组将被显示
11.查询至少有一门课程与学号为's001'的同学所学课程相同的同学的学号和姓名  
select sno,sname from student
where sno in
(select distinct sno from sc 
where cno in (select cno from sc  where sno='s001')) and sno<>'s001' order by sno;
12、把“SC”表中“谌燕”老师教的c002课的成绩都更改为此课程的平均成绩； 
update sc
set score=(select avg(score) from sc a,course b,teacher c
where a.cno=b.cno and b.tno=c.tno and c.tname='谌燕' group by a.cno having a.cno='c002')
where cno='c002'
;
13、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；
select * from sc where sno='s001'
intersect
(select* from sc
minus
select * from sc where sno='s001');
备注：intersect 交集、minus 差集
15.删除学习“谌燕”老师课的SC 表记录
delete  from sc where cno in (select cno from course a,teacher b where a.tno=b.tno and b.tname='谌燕');
16.向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；
select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')
from student st,sc
where not exists
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';
17.查询各课成绩的最高分和最低分，并按照以下形式显示：课程ID,最高分、最低分
select cno as 课程ID,max(score) as 最高分,min(score) as 最低分 from  sc group by cno order by cno;
18.查询各课程的平均成绩和及格率，并按照各课程的平均成绩由低到高显示
select cno,avg(score),sum(case when score>60 then 1 else 0 end)/count(*) as 及格率 from sc group by cno order by avg(score);
19.查询不同老师所教不同课程的平均分从高到低显示
select a.tname,b.cno,avg(c.score) from teacher a,course b,sc c where a.tno=b.tno and b.cno=c.cno group by a.tname,b.cno order by avg(c.score) desc;
20.统计列印各科成绩，各分数段人数:课程ID，课程名称、[100-85]、[85-70]、[70-60]、[<60]
select a.cno as 课程ID,a.cname 课程名称,
sum(case when score between 85 and 100 then 1 else 0 end) as "[100-85]",
sum(case when score between 70 and 85 then 1 else 0 end) as "[85-70]",
sum(case when score between 60 and 70 then 1 else 0 end) as "[70-60]",
sum(case when score<60 then 1 else 0 end) as "[<60]"
from course a,sc b where a.cno=b.cno group by a.cno,a.cname;
21.查询各课成绩前三名的记录(不考虑并列成绩情况)
select * from
(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)
where rn<4; 
备注：应用到row_number()over(partition by order by)
 
练习：根据员工在各自部门中的工资高低排出在部门中的名次(允许并列).
select deptno,empno,ename,sal,rank()over(partition by deptno order by sal) rn from scott.emp;
备注：rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
22.查询每门课程被选修的学生数
select cno,count(sno) from sc group by cno order by cno;
23.查询只选修了一门课程的学生学号和姓名
以下为两种查询方法
select st.sno,st.sname,count(a.cno) from student st,sc a where st.sno=a.sno group by st.sno,st.sname having count(a.cno)=1;
##############################
select sno,sname from
(select st.sno,st.sname,count(a.cno) tt from student st,sc a where st.sno=a.sno  group by st.sno,st.sname) where tt=1;
24.查询男生、女生人数
select distinct
(select count(*) from student where ssex='男') as 男生人数,
(select count(*) from student where ssex='女') as 女生人数
from student;
##############################
select ssex ,count(*) from student group by ssex;
25.查询姓张的学生名单
select * from student where sname like '张%';
26.查询同名同姓的学生名单，并统计同名同姓的学生人数
select sname,count(*) from student group by sname having count(*)>1;
27.查询每门课程的平均成绩，结果按平均成绩升序排列，如果平均成绩相同时，按照课程号降序排列
select cno,avg(score) from sc group by cno order by avg(score),cno desc;
28.查询平均成绩大于85分的所有学生的学号、姓名、平均成绩
select st.sno,st.sname,avg(a.score) from student st,sc a where st.sno=a.sno group by st.sno,st.sname having avg(a.score)>85;
29.查询课程名称为数据库，且分数低于60分学生的学号和姓名
select sname,score,cname from student a,sc b,course c where a.sno=b.sno and b.cno=c.cno and c.cname='数据库' and b.score<60;
30.查询所有学生的选课情况
select a.sname,a.sno,b.cno,c.cname from student a left join sc b on a.sno=b.sno left join course c on b.cno=c.cno;
31.查询任何一门课程成绩在70以上的姓名、课程名称和分数
select a.sname,b.cname,c.score from student a,course b,sc c where a.sno=c.sno and b.cno=c.cno and c.score>70;
32.查询不及格的课程，并按课程号从大到小排列
select a.sno,b.cname,a.score from sc a,course b where a.cno=b.cno and score<60 order by a.cno desc;
33.查询课程编号为c001，并且课程成绩在80分以上的学生的学号和姓名
以下为两种查询方法
select a.sno,a.sname from student a,sc b where a.sno=b.sno and  b.score>80 and b.cno='c001';
##############################
select sno,sname from student where sno in (select sno from sc where sc.score>80 and sc.cno='c001');
34.求选了课程的学生人数
select  count(distinct sno) from sc;
35.查询选修"谌燕"老师所授课程的学生中，成绩最高的学生的姓名和成绩
以下为两种查询方法
select distinct a.sname,b.score from student a,sc b,course c,teacher d
where a.sno=b.sno and b.cno=c.cno and c.tno=d.tno and d.tname='谌燕' and b.score in (select max(score) from sc group by cno);
##############################
select distinct a.sname,b.score from student a,sc b,course c,teacher d
where a.sno=b.sno and b.cno=c.cno and c.tno=d.tno and d.tname='谌燕' and b.score in (select max(score) from sc group by cno);
36.查询各课课程及相应的选修人数
select cno,count(sno) from sc group by cno;
37.查询不同课程成绩相同的学生的学号、课程号、学生成绩
select a.sno,b.cno,a.score from sc a,sc b where a.score=b.score and a.cno<>b.cno;
38.查询每门功课成绩最好的前两名
select * from
(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)
where rn<3;
39.统计每门课程的学生选修人数(超过10人的才统计)。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
select cno,count(sno) from sc group by cno having count(sno)>10 order by count(sno) desc,cno;
 40.查询至少选修两门课程的学生的学号
 以下为两种查询方法
select sno from sc group by sno having count(cno)>1;
##############################
select sno from sc group by sno having count(sno)>1;
41.查询所有被选修的课程的课程号和课程名
以下为两种查询方法
select distinct a.cno,b.cname from sc a,course b where a.cno=b.cno order by cno;
##############################
select cno,cname from course where
cno in (select cno from sc group by cno);
42.查询没有学过“谌燕”老师教的任何一门课的学生姓名
select sname from student st where sno not in (select sno from sc a,course b,teacher c where a.cno=b.cno and b.tno=c.tno and c.tname='谌燕');
43.查询两门以上不及格同学的学号和平均成绩
select sno,avg(score) from sc where sno in (select sno from sc where score<60 group by sno having count(sno)>1) group by sno;
44.检索C004课程成绩小于60分，且按分数降序排列的同学的学号
select sno from sc where cno='c004' and score<60 order by score desc;
45.删除“s002”同学的“c001”课程的成绩
delete from sc where sno='s002' and cno='c001'